package cn.gson.CRM.model.dao.customers;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import cn.gson.CRM.model.entity.Customer;
import cn.gson.CRM.model.entity.Customer.LifeTime;
import cn.gson.CRM.model.vo.CustomerVo;

public interface CustomersDao extends PagingAndSortingRepository<Customer, Long> {
	
	List<Customer> findByNameLike(String name);
	Customer findByNameAndLinkWay(String name,String linkWay);
	
	@Query("select new cn.gson.CRM.model.vo.CustomerVo(c.id,c.name,c.linkMan,c.linkWay,c.lifeTime,c.createDate,"
			+ "c.location,c.email) from Customer c where c.id=?1")
	CustomerVo findCustomerInfo(Long id);

	@Query("select count(*) from Customer c where c.lifeTime = ?1")
	int findCustomerNo(LifeTime lifetime);

	/**
	 * 本月满周年
	 * @return
	 */
	@Query("SELECT count(*) FROM Customer c  WHERE month(c.createDate) = month(CURDATE( ))")
	int findCustomerBir();
	/**
	 * 今天满周年
	 * @return
	 */
	@Query("SELECT count(*) FROM Customer c  WHERE DATE_FORMAT(c.createDate , '%m%d' ) = DATE_FORMAT( CURDATE( ) , '%m%d' )")
	int findCustomerBirToday();
	
	@Query(value="SELECT MONTH (createDate),count(*) FROM customer_  WHERE "
			+"YEAR (createDate) = ?1  and lifeTime = ?2 GROUP BY MONTH (createDate) order by MONTH (createDate) asc",
			nativeQuery=true)
	List<Object[]>  getCustomerData(String year,String state );
}
